package com.lyj.linearalgebra.post.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.lyj.linearalgebra.post.domain.Post;
import com.lyj.linearalgebra.post.domain.Posts;
import com.mchange.v2.c3p0.ComboPooledDataSource;

import cn.itcast.jdbc.TxQueryRunner;

public class PostDao {

	private QueryRunner qr = new TxQueryRunner();

	public void issue(Post form) {

		try {
			String sql = "insert into post values(?,?,?,?,?)";
			Object[] params = { form.getPid(), form.getUid(), form.getTitle(), form.getDescribe(), form.getTime() };
			qr.update(sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	public List<Posts> findAll() {
		try {
			String sql = "select * from posts order by time DESC";
			return qr.query(sql, new BeanListHandler<Posts>(Posts.class));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public List<Post> findByUid(String uid) {

		try {
			String sql = "select * from post where uid=? order by time DESC";
			return qr.query(sql, new BeanListHandler<Post>(Post.class), uid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public Post findByPid(String pid) {

		try {
			String sql = "select * from post where pid=?";
			return qr.query(sql, new BeanHandler<Post>(Post.class), pid);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public String findUsernameByPid(String pid) throws SQLException {

		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		Connection con = dataSource.getConnection();
		String sql = "select username from pid_uname where pid=?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, pid);
		ResultSet rs = ps.executeQuery();
		if(rs.next()) {
			String username = rs.getString("username");
			return username;
		}
		return null;
	}

}
